package apj.demo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import apj.demo.dao.DepartmentDao;
import apj.demo.model.Department;
import apj.demo.util.JDBCUtil;

public class DepartmentDaoImpl implements DepartmentDao{
	
	private static final String SAVE = "insert into department (dep_id,dep_name,leading_offical,description) values(?,?,?,?)";
	private static final String FINDBYID = "select * from department where dep_id = ?";
	private static final String FINDBYALL = "select * from department";
	private final String UPDATE = "update department set dep_name=?,leading_official=?,description=? where dep_id=?";
	
	@Override
	public void save(Department department) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		try{
			conn = JDBCUtil.getConnection();
			prep = conn.prepareStatement(SAVE,Statement.RETURN_GENERATED_KEYS);
			prep.setInt(1, department.getDepId());
			prep.setString(2, department.getDepName());
			prep.setString(3, department.getLeadingOfficial());
			prep.setString(4, department.getDescription());
			
			prep.executeUpdate();
			ResultSet rst = prep.getGeneratedKeys();
			rst.next();
			int id = rst.getInt(1);
			department.setDepId(id);
		}finally{
			JDBCUtil.close(conn);
			JDBCUtil.close(prep);
		}
		
		
	}

	@Override
	public Department findByID(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		Department d = null;
		try{
			conn = JDBCUtil.getConnection();
			prep = conn.prepareStatement(FINDBYID);
			prep.setInt(1, id);
			rst = prep.executeQuery();
			if(rst.next()){
				d = new Department();
				d.setDepName(rst.getString("dep_name"));
				d.setLeadingOfficial(rst.getString("leading_official"));
				d.setDescription(rst.getString("description"));
			}
		}finally{
			JDBCUtil.close(conn);
			JDBCUtil.close(prep);
			JDBCUtil.close(rst);
		}
		return d;
	}

	@Override
	public ArrayList<Department> findByAll() throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		ArrayList<Department> depts = new ArrayList<Department>();
		try{
			Department d = null;
			conn = JDBCUtil.getConnection();
			prep = conn.prepareStatement(FINDBYALL);
			rst = prep.executeQuery();
			while(rst.next()){
				d = new Department();
				
				d.setDepId(rst.getInt("dep_id"));
				d.setDepName(rst.getString("dep_name"));
				d.setLeadingOfficial(rst.getString("leading_official"));
				d.setDescription(rst.getString("description"));
				
				depts.add(d);
			}
		}finally {
			JDBCUtil.close(conn);
			JDBCUtil.close(prep);
			JDBCUtil.close(rst);
		}
		
		return depts;
	}

	@Override
	public void update(Department dep) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(UPDATE);
				ps.setString(1, dep.getDepName());
				ps.setString(2, dep.getLeadingOfficial());
				ps.setString(3, dep.getDescription());
				ps.setInt(4, dep.getDepId());
				ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}

}
